#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use db_parameters;
use ortho_species;

use DBI;


print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    table_ortholog_sets_overlap

	Description:
		Merges the ortholog genes predicted by the different protocols
			in a venn diagram like table (with bit states)
	Populates DB tables:
		orthologs.ortholog_sets_overlap
	Uses
		orthologs.ortholog_sets

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE

#-----------------------------------------------------------------------------------------
#	Get DB parameters
#
use db_parameters;
use ortho_species;
#my @db_param = get_db_parameters();
#
#	Get DB parameters
#-----------------------------------------------------------------------------------------






#
# connect to genome database
#
my $dbh = connect_to_panda();
use get_psql_time;
my $start_time = get_psql_time($dbh);


sub populate_temp_table_summed_by_protocol($$$$)
{
	my ($dbh, $table_name, $where, $protocol_name) = @_;
	my $sql_cmd =<<"PL/SQLCMD";
    CREATE TEMP TABLE $table_name( $protocol_name INT4, gene_id TEXT);
	
    INSERT INTO
        $table_name($protocol_name, gene_id)
        SELECT
            sum(CAST(protocol_id AS INT4)), 
            gene_id
        FROM 
            orthologs.ortholog_sets
        $where
        GROUP BY
            gene_id;
			
    CREATE UNIQUE INDEX i_$table_name on $table_name(gene_id);
PL/SQLCMD
	$dbh->do($sql_cmd);
	#print STDERR $sql_cmd;
}









sub merge_temp_protocol_tables($)
{
	my ($dbh) = @_;
	my $sql_cmd =<<"PL/SQLCMD";
    TRUNCATE TABLE orthologs.one_or_many_orthologs;

	VACUUM ANALYSE orthologs.one_or_many_orthologs;

	INSERT
        INTO orthologs.one_or_many_orthologs
        (
            protocol_id,
            protocol_id_1s,
            protocol_id_manys,
            gene_id,
            species
        )
        SELECT
            t1.protocol_id,
            t2.protocol_id_1s,
            t3.protocol_id_manys,
            c.gene_id,
            c.species
        FROM
            (
                SELECT 
                    DISTINCT ON (gene_id)
                    gene_id, species
                FROM 
                    ens_id
            ) as c JOIN 
            t1 USING (gene_id) LEFT JOIN
            t2 USING (gene_id) LEFT JOIN
            t3 USING (gene_id);


	VACUUM ANALYSE orthologs.one_or_many_orthologs;
PL/SQLCMD
	$dbh->do($sql_cmd);
	#print STDERR $sql_cmd;
}

	# 
	#	Populate temp table for all relationships
	# 
	print STDERR "\tPopulate temp table for all relationships...\n";
	my $where = "WHERE ortholog_type ~ 'to'";
	populate_temp_table_summed_by_protocol($dbh, 't1', $where, 'protocol_id');
	
	# 
	#	Populate temp table for all 1:1 relationships
	# 
	print STDERR "\tPopulate temp table for 1:1 relationships...\n";
    $where = "WHERE ortholog_type ~ 'to' and NOT ortholog_type ~ 'm'";
	populate_temp_table_summed_by_protocol($dbh, 't2', $where, 'protocol_id_1s');

	# 
	#	Populate temp table for all many relationships
	# 
	print STDERR "\tPopulate temp table for all many relationships...\n";
    $where = "WHERE ortholog_type ~ 'to' and ortholog_type ~ 'm'";
	populate_temp_table_summed_by_protocol($dbh, 't3', $where, 'protocol_id_manys');

	print STDERR "\tMerge relationships summed by protocol tables back together\n";
	merge_temp_protocol_tables($dbh);



print STDERR "\tCompleted\n";



log_pipeline_stage($dbh, $curr_protocol_id, 51, 'table ortholog_sets_overlap', $start_time);
$dbh->disconnect();





